package server

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"license/plugin"
	"log"
	"reflect"

	_ "github.com/go-sql-driver/mysql"
)

var mysqldb *sql.DB


type Dbinit struct{
    username string
    password string
    port int
    ipaddress string
    dbname string
} 

//用户信息表
type User struct{
	Id int `json:"id,omitempty"`
	User string `json:"user,omitempty"`
	Token string `json:"token,omitempty"`
	Password string `json:"password,omitempty"`
	Role string `json:"role,omitempty"` //admin

}

/*

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user VARCHAR(255),
    token VARCHAR(255) DEFAULT NULL,
    password VARCHAR(255),
    role VARCHAR(255) DEFAULT NULL
);

*/

func (dbcon Dbinit) dbconnect() *sql.DB{
	
    mysqlurl := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s",dbcon.username,dbcon.password,dbcon.ipaddress,dbcon.port,dbcon.dbname)
	// log.Println(mysqlurl,dbcon.ipaddress,Dconfig.Database.Username)
    db, err := sql.Open("mysql", mysqlurl)
    if err != nil{
		log.Println("mysql connect failed")
        panic(err.Error())
    }
    // defer db.Close()
	db.SetMaxOpenConns(10) // 设置最大打开的连接数
	db.SetMaxIdleConns(5)  // 设置连接池中的最大空闲连接数
	db.SetConnMaxLifetime(0) // 连接最长复用时间，0表示不超时
    return db
}
func dbConnectinit(){
    dbconfig := Dbinit{username: Dconfig.Database.Username,password:Dconfig.Database.Password,port:Dconfig.Database.Port,ipaddress:Dconfig.Database.Host,dbname:Dconfig.Database.DBName}
    mysqldb = dbconfig.dbconnect()
}
type GenData struct{
   PrvKey string `json:"prvKey,omitempty"`
   PubKey string `json:"pubKey,omitempty"`
   ExpirDate string `json:"expirDate,omitempty"`
}

func Insert(license License,Authorization string)bool{
	// prvKey, pubKey := plugin.GenRsaKey() //每个客户生成不同公钥私钥
	prvKey, pubKey := plugin.DefaultRsa() //所有客户生成相同公钥私钥
	var gendata GenData
	gendata.PrvKey = "12345" //客户
	gendata.ExpirDate = license.ExpirDate
	data,err := json.Marshal(gendata)
	if err != nil{
		log.Println("二次数据转json失败")
	}
	ciphertext,signData := plugin.EncryptCtx(string(pubKey),string(prvKey),[]byte(data))
	if ciphertext == "" && signData == ""{
		ciphertext = "xxx"
		signData = "xxx"
		log.Println("数据加密失败")
	}
	insert_sql_cloum_init := "INSERT INTO license(custom, product,expirDate,ownership,privatekey,pubkey,ciphertext,identificationcode"
	insert_sql_value_init := ") VALUES(?,?,?,?,?,?,?,?"
	var Appendvalues []interface{}
	user := QueryuserInfo(Authorization)
	if len(user) == 0{
		license.Ownership = "Admin"
	}else{
		license.Ownership = user[0].User
	}
	Appendvalues = append(Appendvalues, license.Custom,license.Product,license.ExpirDate,license.Ownership,string(prvKey),string(pubKey),ciphertext,signData)
	licenseValue := reflect.ValueOf(license)
	if license.Status != ""{
		 insert_sql_cloum_init += fmt.Sprintf(",%s ", "status")
		 insert_sql_value_init += fmt.Sprintf(",?")
		 Appendvalues = append(Appendvalues,licenseValue.FieldByName("Status").String() )
	}else{
		 insert_sql_cloum_init += fmt.Sprintf(",%s ", "status")
		 insert_sql_value_init += fmt.Sprintf(",?")
		 Appendvalues = append(Appendvalues,"online" )
	}
	if license.Remark != ""{
		 insert_sql_cloum_init += fmt.Sprintf(",%s ", "remark")
		 insert_sql_value_init += fmt.Sprintf(",?")
		 Appendvalues = append(Appendvalues,licenseValue.FieldByName("Remark").String() )
	}
	if license.Ipaddress != ""{
		 insert_sql_cloum_init += fmt.Sprintf(",%s ", "ipaddress")
		 insert_sql_value_init += fmt.Sprintf(",?")
		 Appendvalues = append(Appendvalues,licenseValue.FieldByName("Ipaddress").String() )
	}
	insert_sql_cloum_init += fmt.Sprintf("%s)", insert_sql_value_init)
	stmt, err := mysqldb.Prepare(insert_sql_cloum_init)
	if err != nil{
		log.Println("数据写入失败",err.Error())
    }
	defer stmt.Close()
	_, err = stmt.Exec(Appendvalues...)
    if err != nil{
		log.Println("数据添加失败",err.Error())
        return false
    }
    return true
}

func Delete(license License)bool{
	stmt, _ := mysqldb.Prepare("DELETE FROM license WHERE id=?")
	defer stmt.Close()
	_, err := stmt.Exec(license.Id)
    if err != nil{
        return false
}
    return true
}
func Update(license License)bool{
	intUpdate := "UPDATE license SET "
	var Appendvalues []interface{}
	// stmt, err := mysqldb.Prepare("UPDATE license SET remark=?,ipaddress=?,ownership=?,status=?,expirDate=? WHERE id=?")
	licenseValue := reflect.ValueOf(license)
	if license.Remark != ""{
		 if len(Appendvalues) == 0{
			 intUpdate += fmt.Sprintf("%s=? ", "remark")
		 }else{
			 intUpdate += fmt.Sprintf(",%s=? ", "remark")
		 }
		 
		 Appendvalues = append(Appendvalues,licenseValue.FieldByName("Remark").String() )
	}
	if license.Ipaddress != ""{
		 if len(Appendvalues) == 0{
			 intUpdate += fmt.Sprintf("%s=? ", "ipaddress")
		 }else{
			 intUpdate += fmt.Sprintf(",%s=? ", "ipaddress")
		 }
		Appendvalues = append(Appendvalues,licenseValue.FieldByName("Ipaddress").String() )
	}
	if license.Ownership != ""{
		 if len(Appendvalues) == 0{
			 intUpdate += fmt.Sprintf("%s=? ", "ownership")
		 }else{
			 intUpdate += fmt.Sprintf(",%s=? ", "ownership")
		 }
		Appendvalues = append(Appendvalues,licenseValue.FieldByName("Ownership").String() )
	}
	if license.Status != ""{
		 if len(Appendvalues) == 0{
			 intUpdate += fmt.Sprintf("%s=? ", "status")
		 }else{
			 intUpdate += fmt.Sprintf(",%s=? ", "status")
		 }
		Appendvalues = append(Appendvalues,licenseValue.FieldByName("Status").String() )
	}
	if license.ExpirDate != ""{
		ciphertext,signData := QueryKey(license.Id,license.ExpirDate)
		// log.Println(ciphertext,signData)
		if ciphertext == "" && signData == ""{
			ciphertext = "xxx"
			signData = "xxx"
		}
		 if len(Appendvalues) == 0{
			 intUpdate += fmt.Sprintf("%s=? ", "expirDate")
		 }else{
			 intUpdate += fmt.Sprintf(",%s=? ", "expirDate")
		 }
			Appendvalues = append(Appendvalues,licenseValue.FieldByName("ExpirDate").String() )
			intUpdate += fmt.Sprintf(",%s=? ", "ciphertext")
			Appendvalues = append(Appendvalues,ciphertext )
			intUpdate += fmt.Sprintf(",%s=? ", "identificationcode")
			Appendvalues = append(Appendvalues,signData )
	}
	intUpdate += " WHERE id=?"
	Appendvalues = append(Appendvalues, licenseValue.FieldByName("Id").Int())
	// log.Println(intUpdate,Appendvalues)
	stmt, err := mysqldb.Prepare(intUpdate)
	defer stmt.Close()
	if err != nil{
		log.Println(err.Error())
		panic(err)
	}
	_, err = stmt.Exec(Appendvalues...)
	if err != nil {
		return false
	}
    return true
}

func Query()[]License{
	rows, _ := mysqldb.Query("SELECT id,custom,expirDate,product,status,identificationcode,ownership,ipaddress,remark,create_time,update_time FROM license")
	defer rows.Close()
	var licenses []License
	var (
		Status,IdentificationCode,Ipaddress,Remark sql.NullString
		)
	for rows.Next() {
		var license License
		err := rows.Scan(&license.Id, &license.Custom, &license.ExpirDate, &license.Product, &Status,&IdentificationCode,&license.Ownership,&Ipaddress,&Remark,&license.CreateTime,&license.UpdateTime)
        if err != nil{
			log.Println(err.Error())
            panic(err.Error())
        }
		license.Status = CheckNull(Status)
		license.IdentificationCode = CheckNull(IdentificationCode)
		license.Ipaddress = CheckNull(Ipaddress)
		license.Remark = CheckNull(Remark)
		licenses = append(licenses, license)
	}
	return licenses
}

func CheckNull(cloum sql.NullString)(string){
	if cloum.Valid{
		return cloum.String
	}else{
		return ""
	}
}

func UpdateStatus()(bool){
	intUpdate := "UPDATE license SET status = CASE WHEN expirDate > CURDATE() THEN 'online' ELSE 'offline'END;"
	stmt, err := mysqldb.Prepare(intUpdate)
	defer stmt.Close()
	if err != nil{
		log.Println(err.Error())
		panic(err)
	}
	_, err = stmt.Exec()
	if err != nil {
		return false
	}
    return true
}

func QueryKey(id int,ExpirDate string)(string,string){
	querySql := fmt.Sprintf("SELECT privatekey,pubkey FROM license where id=%d",id)
	rows, err := mysqldb.Query(querySql)
	if err != nil{
		log.Println("mysql QueryKey failed")
		panic(err.Error())
	}
	defer rows.Close()
	var genDatas []GenData
	for rows.Next() {
		var genData GenData
		err := rows.Scan(&genData.PrvKey,&genData.PubKey)
        if err != nil{
            panic(err.Error())
        }
		genDatas = append(genDatas, genData)
	}
	var gendataUpdate GenData
	gendataUpdate.PrvKey = "12345" //客户
	gendataUpdate.ExpirDate = ExpirDate
	data,err := json.Marshal(gendataUpdate)
	if err != nil{
		log.Println("二次数据转json失败")
	}
	ciphertext,signData := plugin.EncryptCtx(genDatas[0].PubKey,genDatas[0].PrvKey,data)
	return ciphertext,signData
}

func QueryExpir()[]License{
	UpdateStatus()
	querySql := fmt.Sprintf("SELECT id,custom,expirDate,product,status,ownership FROM license where status = 'online' and expirDate < CURDATE() + INTERVAL %d DAY",Dconfig.Server.Expirdays)
	rows, err := mysqldb.Query(querySql)
	if err != nil{
		log.Println("mysql QueryExpir failed")
		panic(err.Error())
	}
	defer rows.Close()
	var licenses []License
	for rows.Next() {
		var license License
		err := rows.Scan(&license.Id, &license.Custom, &license.ExpirDate, &license.Product, &license.Status,&license.Ownership)
        if err != nil{
            panic(err.Error())
        }
		licenses = append(licenses, license)
	}
	return licenses
}
type GetKey struct{
		privatekey string
		ciphertext string
}

func QueryRSA(id int)([]GetKey){
	Querysql := fmt.Sprintf("SELECT privatekey,ciphertext FROM license where id = %d",id)
	rows, _ := mysqldb.Query(Querysql)
	defer rows.Close()
	var getkeys []GetKey
	for rows.Next(){
		var getkey GetKey
		err := rows.Scan(&getkey.privatekey,&getkey.ciphertext)
	    if err != nil{
		    panic(err)
	    }
		getkeys = append(getkeys,getkey )
	}
	return getkeys
}

func QueryuserInfo(Authorization string)[]User{
	Querysql := fmt.Sprintf("SELECT id,role,user,token FROM user where token = \"%s\"",Authorization)
	rows, _ := mysqldb.Query(Querysql)
	defer rows.Close()
	var users []User
	for rows.Next() {
		var user User
		err := rows.Scan(&user.Id,&user.Role,&user.User,&user.Token)
        if err != nil{
            panic(err)
        }
		users = append(users, user)
	}
	return users
}
func CreateUserMode(user CreUser,token string)bool{
	// InsertUserSql := "INSERT INTO user(user,token) values()"
	InsertUserSql := "INSERT INTO user(user,token,role) VALUES(?,?,?)"
	log.Println(InsertUserSql)
	stmt, err := mysqldb.Prepare(InsertUserSql)
	if err != nil{
		log.Println("数据写入失败",err.Error())
    }
	defer stmt.Close()
	_, err = stmt.Exec(user.Username,token,user.Role)
    if err != nil{
		log.Println("数据添加失败",err.Error())
        return false
    }
    return true
}